
package trabalhotec.BD;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

public class Cliente {
    Connection con;
    Statement stmt;
    public void cadCliente(long cpf,String nome,String email,char sexo)throws SQLException{
            con = null;
            stmt = null;
            con = Conexao.abrirConexao();
            stmt = con.createStatement();
            String inserir = "Insert into cliente values ('"+nome+"',"+cpf+",'"+email+"','"+sexo+"')";
            stmt.executeUpdate(inserir);
            stmt.close();
            con.close();
    }
    public Vector<Vector<String>> buscaCliente(String nome, Long cpf)throws SQLException{
        con = null;
        stmt = null;
        Vector<Vector<String>> clientes = new Vector<Vector<String>>();
        con = Conexao.abrirConexao();
        stmt = con.createStatement();
        Long cpf2;
        String buscar=null;
        if(cpf == null){
            buscar = "Select * from cliente where Nome LIKE '%"+nome+"%'";
        }else
            buscar = "Select * from cliente where CPF LIKE '%"+cpf+"%'";
        ResultSet rs = stmt.executeQuery(buscar);
        while(rs.next()){
            Vector<String> list = new Vector<String>();
            list.add(rs.getString("Nome"));
            cpf2 = rs.getLong("CPF");
            list.add(cpf2.toString());
            list.add(rs.getString("Email"));
            list.add(rs.getString("Sexo"));
            clientes.add(list);
        }
        try{
        stmt.close();
        con.close();
        }catch(SQLException e){
            System.out.println("erro ao fechar conexao");
        }
        return clientes;
    }
    public Vector<Vector<String>> voosdecliente(Long CPF){
            Vector<Vector<String>> voos = new Vector<Vector<String>>();
            con = null;
            stmt = null;
            Statement stmt2 = null;
            try {
                con = Conexao.abrirConexao();
                stmt = con.createStatement();
                stmt2 = con.createStatement();
                String cod = null;
                String clievoos = "Select * from passagens where codCli=" + CPF;
                ResultSet rs = stmt.executeQuery(clievoos);
                while (rs.next()) {
                    cod = rs.getString("Cod_voo");
                    String buscvoos = "Select * from voos where Codigo = '" + cod + "'";
                    ResultSet rs2 = stmt2.executeQuery(buscvoos);
                    System.out.println(buscvoos);
                    Vector list = new Vector();
                    while(rs2.next()){
                        list.add(rs2.getString("Codigo"));
                        list.add(rs2.getString("Origem"));
                        list.add(rs2.getString("Destino"));
                        list.add(DataUtil.obterDataDoBanco(rs2.getTimestamp("Datadepartida")).toString());
                        list.add(DataUtil.obterDataDoBanco(rs2.getTimestamp("Datadechegada")).toString());
                        list.add("" + rs2.getInt("Tempodevoo"));
                        voos.add(list);
                    }
                }
            } catch (SQLException ex) {
             System.out.println("erro ao acessar banco de dados");
            }finally{
                try {
                    stmt.close();
                    con.close();
                } catch (SQLException e) {
                System.out.println("Erro ao fechar conexao");
                }
            }
            return voos;
    }
    public boolean vercli(String cpf){
        stmt = null;
        con = null;
        boolean ver = false;
        try {
            con = Conexao.abrirConexao();
            stmt = con.createStatement();
            String verificar = "Select * from cliente where CPF = '"+cpf+"'";
            ResultSet rs = stmt.executeQuery(verificar);
            if (rs.next()){
                ver = true;
            }else ver = false;
        } catch (SQLException ex) {
            System.out.println("erro ao acessar banco de dados");
        }finally{
            try {
                stmt.close();
                con.close();
            } catch (SQLException ex) {
                System.out.println("Erro ao fechar conexao");
            }
            return ver;
        }
    }
}
